﻿CREATE PROCEDURE GetCoachInformation
AS
	SELECT 
	coachDB.Inventory_Number,
	coachDB.Cargo_Weight,
	station.Name AS Destination_Name,
	cargo.Name AS Cargo_Name,
	shipmentClient.Name AS ShipmentClient_Name,
	coachDB.Park_Type,
	startStation.Name AS Start_Station_Name,
	carOperation.Name AS Operation_Name,
	coachDB.Operation_Data,
	coachDB.Operation_Year,
	coachDB.Operation_Time,
	stationOfOperation.Name AS Station_Of_Operation_Name,
	deliveryRoad.Name AS Delivery_Road_Name,
	receptionRoad.Name AS Reception_Road_Name,
	coachDB.Coach_Index,
	coachDB.Coach_Number
FROM
	dbo.CoachDB AS coachDB
	inner join dbo.Station AS station ON coachDB.Destionation_FK = station.Code 
	inner join dbo.Cargo AS cargo ON coachDB.Good_Code_FK = cargo.ShortCode
	inner join dbo.ShipmentClient AS shipmentClient ON coachDB.Receiver_Code_FK = shipmentClient.Code
	inner join dbo.Station AS startStation ON coachDB.Start_Station_FK = startStation.Code
	inner join dbo.CarOperation AS carOperation ON coachDB.Operation_Code_FK = carOperation.Code
	inner join dbo.Station AS stationOfOperation ON coachDB.Station_Of_Operation_FK = stationOfOperation.Code
	inner join dbo.Road AS deliveryRoad ON coachDB.Delivery_Road_FK = deliveryRoad.Code
	inner join dbo.Road AS receptionRoad ON coachDB.Reception_Road_FK = receptionRoad.Code;

